﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace DevTeam
{
    public class DataNotifier
    {
      
        private SqlConnection cnn = null;
        private string ConnectionString;

        public delegate void DataChanged();
        public event DataChanged OnDataChanged;

        public DataNotifier(string cnnString)
        {
            try
            {
                SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
                ConnectionString = cnnString;
                perm.Demand();
            }
            catch
            {
                throw new ApplicationException("No permission");
            }

            // Stop an existing services on this connection string just be sure
            SqlDependency.Stop(cnnString);

            // Start the dependency
            // User must have SUBSCRIBE QUERY NOTIFICATIONS permission
            // Database must also have SSB enabled
            // ALTER DATABASE Chatter SET ENABLE_BROKER
            SqlDependency.Start(cnnString);

            cnn = new SqlConnection(cnnString);
        }

        ~DataNotifier()
        {
            SqlDependency.Stop(ConnectionString);
        }

        public DataTable GetData(string storedProcedureName)
        {
            DataTable dt = new DataTable();

            try
            {
                // Create command
                // Command must use two part names for tables
                // SELECT <field> FROM dbo.Table rather than 
                // SELECT <field> FROM Table
                // Query also can not use *, fields must be designated
                SqlCommand cmd = new SqlCommand(storedProcedureName, cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                
                // Clear any existing notifications
                cmd.Notification = null;

                // Create the dependency for this command
                SqlDependency dependency = new SqlDependency(cmd);

                // Add the event handler
                dependency.OnChange += new OnChangeEventHandler(OnChange);

                // Open the connection if necessary
                if (cnn.State == ConnectionState.Closed)
                    cnn.Open();

                // Get the messages
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        void OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;

            // Notices are only a one shot deal
            // so remove the existing one so a new 
            // one can be added
            dependency.OnChange -= OnChange;

            // Fire the event
            if (OnDataChanged != null)
            {
                OnDataChanged();
            }
        }
    }
}
